package com.niit.bookmanage.service.impl;

import com.niit.bookmanage.Utils.AbnormalUtils;
import com.niit.bookmanage.domain.vo.BookDurationVO;
import com.niit.bookmanage.hive.PlatformDictionary;
import com.niit.bookmanage.service.DataService;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Liweijian.
 * @date 2024/6/8 20:37
 */
@Service
public class DataImpl implements DataService {

    @Override
    public List<BookDurationVO> getBookDuration() {

        List<BookDurationVO> bookDurationVOS = new ArrayList<>();
        List<String> sqlList = new ArrayList<>();
        int count;
        List<Integer> countList = new ArrayList<>();
        String sqlA = "SELECT COUNT(*) AS count\n" +
                      "FROM library2\n" +
                      "WHERE DATEDIFF(return_time, borrow_time) <= 7 " ;
        sqlList.add(sqlA);
        String sqlB = "SELECT COUNT(*) AS count\n" +
                      "FROM library2\n" +
                      "WHERE DATEDIFF(return_time, borrow_time) > 7 AND DATEDIFF(return_time, borrow_time) <= 14 " ;
        sqlList.add(sqlB);
        String sqlC = "SELECT COUNT(*) AS count\n" +
                      "FROM library2\n" +
                      "WHERE DATEDIFF(return_time, borrow_time) > 14 AND DATEDIFF(return_time, borrow_time) <= 30 " ;
        sqlList.add(sqlC);
        try {
            init();
            ResultSet rs = null;
            for (int i = 0; i <= sqlList.size() - 1; i++) {
                String sql = sqlList.get(i);
                System.out.println("Running" + sql);
                PreparedStatement pstmt = conn.prepareStatement(sql);
                rs = pstmt.executeQuery();
                if (rs.next()) {
                    count = rs.getInt("count");
                    countList.add(count);
                }
            }
        } catch (SQLException e) {
            Object object = AbnormalUtils.getAbnormal(e);
            System.err.println(object);
        } finally {
            destroy();
        }
        BookDurationVO vo = new BookDurationVO("小于7天", countList.get(0));
        BookDurationVO vo1 = new BookDurationVO("7-15天", countList.get(1));
        BookDurationVO vo2 = new BookDurationVO("15-30天", countList.get(2));
        bookDurationVOS.add(vo);
        bookDurationVOS.add(vo1);
        bookDurationVOS.add(vo2);
        return bookDurationVOS;
    }

    private static Connection conn = null;

    private static void init() {
        try {
            Class.forName(PlatformDictionary.DRIVER_NAME);
            conn = DriverManager.getConnection(PlatformDictionary.URL, PlatformDictionary.USER_NAME,
                    PlatformDictionary.PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            Object object = AbnormalUtils.getAbnormal(e);
            System.err.println(object);
        }
    }

    /**
     * 释放资源
     */
    private static void destroy() {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            Object object = AbnormalUtils.getAbnormal(e);
            System.err.println(object);
        }
    }

}
